package Dao;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import Conexao.ConnectionManager;
import Modelo.Bibliotecario;

public class BibliotecarioDaoImp implements BibliotecarioDAO{

	
	private final String INSERT = "insert into bibliotecario (nome, cpf, nascimento, sexo, login, senha, telefone) values (?,?,?,?,?,?,?)";
	private final String UPDATE = "update pessoa set nome = ?, cpf = ?, nascimento = ?, endereco = ?, sexo = ?, idbibliotecario = ?, telefone = ?";
	private final String DELETE = "delete from bibliotecario where idbibliotecario = ?";
	private final String ALL = "select * from bibliotecario";
	private final String CPF = "select * from bibliotecario where cpf = ?";
	private final String NOME = "select * from bibliotecario where nome = ?";
	private final String LOGIN = "select * from Bibliotecario where login = ? and senha = ?";
	
	
	private static Connection connection;

	public BibliotecarioDaoImp() {
		openConnection();
		
	}
	
	public Bibliotecario salvar(Bibliotecario e) {
		try {
			PreparedStatement ps = null;
			
			ps = ConnectionManager.getInstance().getConnection().prepareStatement(INSERT);
			
			ps.setObject(1, e.getNome());
			ps.setObject(2, e.getCpf());
			ps.setObject(3, e.getNascimento());
			ps.setObject(4, e.getSexo());
			ps.setObject(5, e.getLogin());
			ps.setObject(6, e.getSenha());
			ps.setObject(7, e.getTelefone());
			
			ps.executeUpdate();
			
		} catch (SQLException ex) {
			Logger.getLogger(BibliotecarioDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return e;
	}

		
	public void alterar(Bibliotecario e) {
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(UPDATE);
						
			ps.setObject(1, e.getNome());
			ps.setObject(2, e.getCpf());
			ps.setObject(3, e.getNascimento());
			ps.setObject(4, e.getSexo());
			ps.setObject(5, e.getLogin());
			ps.setObject(6, e.getSenha());
			ps.setObject(7, e.getTelefone());
			
			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(BibliotecarioDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
	}
		
	
	public void deletar(Bibliotecario e) {
		try {
			PreparedStatement ps = null;
			ps = connection.prepareStatement(DELETE);

			ps.setObject(1, e.getIdBibliotecario());
			
			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(BibliotecarioDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
	}

	
	public List<Bibliotecario> findAll() {
		List<Bibliotecario> bibliotecarios = new ArrayList<Bibliotecario>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(ALL);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Bibliotecario bibliotecario = new Bibliotecario();
				bibliotecario.setIdBibliotecario(rs.getInt("idbibliotecario"));
				bibliotecario.setNome(rs.getString("nome"));
				bibliotecario.setCpf(rs.getString("cpf"));
				bibliotecario.setNascimento(rs.getDate("Nascimento"));
				bibliotecario.setSexo(rs.getString("sexo"));
				bibliotecario.setTelefone(rs.getString("Telefone"));
				bibliotecarios.add(bibliotecario);
			}

		} catch (SQLException ex) {
			Logger.getLogger(BibliotecarioDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return bibliotecarios;
	}
	
	public Bibliotecario buscarBibliotecarioPorCpf(String cpf) {
		List<Bibliotecario> bibliotecarios = new ArrayList<Bibliotecario>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(CPF);
			ps.setObject(1, cpf);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Bibliotecario bibliotecario = new Bibliotecario();
				bibliotecario.setIdBibliotecario(rs.getInt("idbibliotecario"));
				bibliotecario.setNome(rs.getString("nome"));
				bibliotecario.setCpf(rs.getString("cpf"));
				bibliotecario.setNascimento(rs.getDate("Nascimento"));
				bibliotecario.setSexo(rs.getString("sexo"));
				bibliotecario.setTelefone(rs.getString("Telefone"));
				bibliotecarios.add(bibliotecario);
			}

		} catch (SQLException ex) {
			Logger.getLogger(BibliotecarioDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		if (bibliotecarios.isEmpty()) {
			return null;
		} else {
			return bibliotecarios.get(0);
		}
	}

	
	
	public Bibliotecario buscarBibliotecarioPorNome(String nome) {
		List<Bibliotecario> bibliotecarios = new ArrayList<Bibliotecario>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(NOME);
			ps.setObject(1, nome);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Bibliotecario bibliotecario = new Bibliotecario();
				bibliotecario.setIdBibliotecario(rs.getInt("idbibliotecario"));
				bibliotecario.setNome(rs.getString("nome"));
				bibliotecario.setCpf(rs.getString("cpf"));
				bibliotecario.setNascimento(rs.getDate("Nascimento"));
				bibliotecario.setSexo(rs.getString("sexo"));
				bibliotecario.setTelefone(rs.getString("Telefone"));
				bibliotecarios.add(bibliotecario);
			}

		} catch (SQLException ex) {
			Logger.getLogger(BibliotecarioDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		if (bibliotecarios.isEmpty()) {
			return null;
		} else {
			return bibliotecarios.get(0);
		}
	}
	
	
	public boolean isConnectionClose() {
		try {
			return connection.isClosed();
		} catch (SQLException ex) {
			return false;
		}
	}

	
	@Override
	public void openConnection() {
		if ( connection == null) {
			connection = ConnectionManager.getInstance().getConnection();
		}
		
		if ( isConnectionClose() ) {
			connection = ConnectionManager.getInstance().getConnection();
		}		
	}

	@Override
	public void closeConnection() {
		if ( connection != null && !this.isConnectionClose()) {
			ConnectionManager.getInstance().closeConnection(connection);
		}
		
	}

	@Override
	public boolean login(String login, String senha) {
		List<Bibliotecario> bibliotecarios = new ArrayList<Bibliotecario>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(LOGIN);
			ps.setObject(1, login);
			ps.setObject(2, senha);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Bibliotecario bibliotecario = new Bibliotecario();
				bibliotecario.setLogin(rs.getString("login"));
				bibliotecario.setSenha(rs.getString("senha"));
				bibliotecarios.add(bibliotecario);
			}

		} catch (SQLException ex) {
			Logger.getLogger(BibliotecarioDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		if (bibliotecarios.isEmpty()) {
			return false;
		} else {
			return true;
		}
	}
	
	
}


